/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package person.report;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.context.FacesContext;
import javax.naming.NamingException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import system.general.Exc;
import system.general.Logged;
import system.general.Persistencia;

/**
 *
 * @author gilberto-CIDITIC
 */
@ManagedBean(name = "person_professionalReport")
@RequestScoped
public class Professional {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private ResultSet rsts;
    private String sql, tableName;
    private system.general.Label label = new system.general.Label();

    public Professional() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
        tableName = "reportes.persona_professional_" + String.valueOf(logged.getLoggedId());
    }

    public void report() throws Exc, SQLException, NamingException, Exception {
        try {
            system.general.Path path = new system.general.Path();
            //Elimina la tabla
            dropTable();

            //Crea la tabla 
            createTable();

            //inserta los datos
            loadData();

            Map params = new HashMap();
            params.put("table", tableName);
            params.put("SUBREPORT_DIR", path.getPersonReportPath());
            String jasperReport = path.getPersonReportPath() + "profesional.jasper";
            this.persistencia.dbConnect();
            createReport(jasperReport, params, persistencia.getConn());
        } catch (JRException ex) {
        } catch (Exception ex) {
        } finally {
            this.persistencia.dbDisconnect();
        }
    }

    private void loadData() throws Exc, SQLException, NamingException, Exception {
        try {

            sql = "SELECT pp.id_persona, lp.nombre as pais, pp.idl_cargo as cargo, pp.empresa, pp.inicio, "
                    + "pp.fin, pp.referencias, pp.departamento, pp.detalle "
                    + "FROM persona_profesional pp "
                    + "INNER JOIN localizacion_pais lp "
                    + "ON (pp.id_pais = lp.id) "
                    + "WHERE pp.id_persona = " + logged.getMasterId();

            rsts = persistencia.executeRsts(sql);
            while (rsts.next()) {
                sql = "INSERT INTO " + tableName + " "
                        + "(titulo, id_persona, pais, cargo, empresa, inicio, fin,"
                        + "referencias, departamento, detalle) "
                        + "VALUES ('" + label.name("person.report", "title3") + "',"
                        + rsts.getInt("id_persona") + ", "
                        + "'" + rsts.getString("pais") + "',"
                        + "'" + label.name("person.professional", "cargo" + rsts.getString("cargo")) + "',"
                        + "'" + rsts.getString("empresa") + "',"
                        + "'" + rsts.getDate("inicio") + "',"
                        + "'" + rsts.getDate("fin") + "',"
                        + "'" + rsts.getString("referencias") + "',"
                        + "'" + rsts.getString("departamento") + "',"
                        + "'" + rsts.getString("detalle") + "')";
                persistencia.executeUpdate(sql);
            }
        } catch (Exc ex) {
            String e = ex.toString();
        } catch (SQLException ex) {
            String e = ex.toString();
        } catch (Exception ex) {
            String e = ex.toString();
        } finally {
            persistencia.dbDisconnect();
        }
    }

    private void dropTable() throws Exc, SQLException, NamingException, Exception {
        sql = "DROP TABLE IF EXISTS " + tableName;
        persistencia.executeUpdate(sql);
    }

    private void createTable() throws Exc, SQLException, NamingException, Exception {
        sql = "CREATE TABLE " + tableName
                + " (titulo character varying (300), "
                + "id_persona integer, "
                + "pais character varying(50) NOT NULL, "
                + "cargo character varying(50) NOT NULL, "
                + "inicio date NOT NULL, "
                + "fin date NOT NULL, "
                + "empresa character varying(100) NOT NULL, "
                + "referencias text, "
                + "departamento character varying(300), "
                + "detalle character varying(400)) "
                + "WITH (OIDS=FALSE); "
                + "ALTER TABLE " + tableName
                + " OWNER TO core;";

        persistencia.executeUpdate(sql);
    }

    public void createReport(String archivoJasper, Map params, Connection ds) throws JRException, IOException {
        JasperPrint jasperPrint = JasperFillManager.fillReport(archivoJasper, params, ds);
        HttpServletResponse httpServletResponse = (HttpServletResponse) FacesContext.getCurrentInstance().getExternalContext().getResponse();
        ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, servletOutputStream);
    }

    public String getTableName() {
        return tableName;
    }
}
